from . import db
from sqlalchemy.types import JSON
from uuid import uuid4
from sqlalchemy import func, DateTime
from sqlalchemy.dialects.mysql import BIGINT

class Elderdemand(db.Model):
    __tablename__ = 'elder_demand'
    id = db.Column(db.BigInteger, primary_key=True)
    elder = db.Column(db.String(255), nullable=False)
    actions = db.Column(db.String(255), nullable=False)
    arrange = db.Column(db.String(1), default='0')  # 新增字段，默认值为'0'

    def __repr__(self):
        return f'<Elder {self.id} {self.elder} arrange:{self.arrange}>'

class Attendantskills(db.Model):
    __tablename__ = 'attendant_skills'
    id = db.Column(db.String(255), primary_key=True, default=lambda: str(uuid4()))
    attendant_name = db.Column(db.String(255), nullable=False)
    skills = db.Column(db.String(255), nullable=False)

class CaregiverSchedule(db.Model):
    __tablename__ = 'caregiver_schedules'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(db.String(36), db.ForeignKey('attendant_skills.id'), nullable=False)
    schedule_date = db.Column(db.DateTime, nullable=False)
    time_slot = db.Column(db.Integer, nullable=False)
    is_available = db.Column(db.Boolean, default=True)

    def __repr__(self):
        return f'<CaregiverSchedule {self.caregiver_id} {self.schedule_date} {self.time_slot}>'

class DicNursingAction(db.Model):
    __tablename__ = 'dic_nursing_actions'
    id = db.Column(db.BigInteger, primary_key=True)
    pid = db.Column(db.BigInteger)
    name = db.Column(db.String(255), nullable=False)
    level = db.Column(db.String(255))
    nursing_level = db.Column(db.String(255))

    def __repr__(self):
        return f'<DicNursingAction {self.id} {self.name}>'

class ScheduleResult(db.Model):
    __tablename__ = 'schedule_results'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    elder = db.Column(db.String(36), nullable=False)
    schedule_date = db.Column(DateTime, nullable=False)
    schedule_data = db.Column(JSON, nullable=False)
    # 新增字段
    plan_date = db.Column(db.Date, nullable=True, index=True)
    batch_number = db.Column(db.Integer, nullable=True, index=True)
    create_time = db.Column(db.DateTime, nullable=True, default=func.now())
    update_time = db.Column(db.DateTime, nullable=True, default=func.now(), onupdate=func.now())
    status = db.Column(db.String(20), nullable=True, default='active')

    def __repr__(self):
        return f'<ScheduleResult {self.id} {self.elder} {self.schedule_date}>'

class TimeSlot(db.Model):
    __tablename__ = 'time_slots'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    schedule_result_id = db.Column(db.String(36), db.ForeignKey('schedule_results.id'), nullable=False)
    elder = db.Column(db.String(36), nullable=False)
    time_slot = db.Column(db.String(11), nullable=False)
    activity = db.Column(db.String(100), nullable=False)
    activity_id = db.Column(db.String(100), nullable=False)
    caregiver = db.Column(db.String(100), nullable=False)
    caregiver_id = db.Column(db.String(100), nullable=False)
    time_order = db.Column(db.Integer, nullable=False)
    schedule_date = db.Column(DateTime, nullable=False)
    # 新增字段
    plan_date = db.Column(db.Date, nullable=True, index=True)
    batch_number = db.Column(db.Integer, nullable=True, index=True)
    create_time = db.Column(db.DateTime, nullable=True, default=func.now())
    update_time = db.Column(db.DateTime, nullable=True, default=func.now(), onupdate=func.now())
    status = db.Column(db.String(20), nullable=True, default='active')

    def __repr__(self):
        return f'<TimeSlot {self.id} {self.elder} {self.time_slot} {self.activity}>'

    @classmethod
    def get_ordered_slots(cls):
        return cls.query.order_by(
            cls.schedule_result_id,
            cls.elder,
            cls.schedule_date,
            func.substr(cls.time_slot, 1, 2),
            func.substr(cls.time_slot, 4, 2)
        ).all()

class ShiftAssignment(db.Model):
    __tablename__ = 'shift_assignments'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(db.String(255), db.ForeignKey('attendant_skills.id'), nullable=False)
    caregiver_name = db.Column(db.String(255), nullable=False)
    shift_date = db.Column(db.Date, nullable=False)
    shift_type = db.Column(db.String(20), nullable=False)  # 'morning', 'afternoon', 'night'
    shift_start_time = db.Column(db.Time, nullable=False)
    shift_end_time = db.Column(db.Time, nullable=False)
    schedule_type = db.Column(db.String(20), nullable=False, default='daily')  # 'daily', 'weekly', 'monthly', 'quarterly', 'yearly'
    created_at = db.Column(db.DateTime, nullable=False, default=func.now())
    updated_at = db.Column(db.DateTime, nullable=False, default=func.now(), onupdate=func.now())

    def __repr__(self):
        return f'<ShiftAssignment {self.id} {self.caregiver_name} {self.shift_date} {self.shift_type}>'

    @classmethod
    def get_assignments_by_date_range(cls, start_date, end_date, date_desc=True):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case([
            (cls.shift_type == '班次1', 1),
            (cls.shift_type == '班次2', 2),
            (cls.shift_type == '班次3', 3)
        ], else_=4)

        query = cls.query.filter(
            cls.shift_date >= start_date,
            cls.shift_date <= end_date
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()

    @classmethod
    def get_assignments_by_caregiver(cls, caregiver_id, start_date, end_date, date_desc=True):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case([
            (cls.shift_type == '班次1', 1),
            (cls.shift_type == '班次2', 2),
            (cls.shift_type == '班次3', 3)
        ], else_=4)

        query = cls.query.filter(
            cls.caregiver_id == caregiver_id,
            cls.shift_date >= start_date,
            cls.shift_date <= end_date
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()